--4/2011
--Authors: Tyler Smith, Gordon Stein, Aaron Kinder, Taylor Fouts
--MCS3543 Professor El Bathy

drop user project cascade;
create user project identified by project;
grant connect,resource,create session to project;

connect project/project;
--these sequences create unique PK for various attributes
Create sequence HomeID start with 1 increment by 1;
Create sequence AgentID start with 1 increment by 1;
Create sequence ApplianceID start with 1 increment by 1;
--Users can add agents who recieve a unique, sequentially generated agentID (see: above)
Create Table Agents
(
	AgentID integer,
	AgentName varchar(30),
	Primary key(AgentID)
);
--Users can creat a home, specifying mulitple attributes.  The homes also recieve a unique, sequentially generated agentID.
Create Table Homes
(
	HomeID integer,
	Address varchar(50),
	City varchar(30),
	State varchar(30),
	Zipcode number(5),
	FloorSpace integer,
	Floors integer,
	BedRooms integer,
	Bathrooms integer,
	LandSize integer,
	Year number(4),
	HouseClass varchar(15),
	Primary key(HomeID)
);
--The Appliances table allows the user to add appliances to a house.  The appliances are linked via the HomeID FK from homes.  A single home can have many appliances (1:m)
Create Table Appliances
(
	ApplianceID integer,
	HomeID integer,
	AppType varchar(25),
	Maker varchar(25),
	Primary Key(ApplianceID),
	Foreign key(HomeID) references Homes(HomeID)
);
/*
Create Table HomeOwner
(
	ssn number(9),
	Name varchar(25),
	Dependants integer,
	Income number(8,2),
	Age integer,
	profession varchar(25),
	Primary key(ssn)
);
*/
/*
Create Table HomeOwnership
(
	ssn number(9),
	HomeID integer,
	beginDate varchar2(10),
	endDate date,
	Foreign key(ssn) references HomeOwner(ssn),
	Foreign key(HomeID) references Homes(HomeID)
);
*/
--Market table enables the user to add their home to the "market" where they can specify price and select a realtor.  The homeID is used to uniquely identify a house.
Create Table Market
(
	HomeID integer,
	AgentID integer,
	Price integer,
	startDate varchar(10),
	Foreign key(HomeID) references Homes(HomeID),
	Foreign key(AgentID) references Agents(AgentID)
);

--INSERTS

--Insert data into home table
insert into homes values (HomeID.NEXTVAL,'30833 Crest Forest','Farmington Hills','MI',48331,1200,1,2,1,0,2000,'Apartment');
insert into homes values (HomeID.NEXTVAL,'23423 Adams Blvd','Farmington Hills','MI',48331,1500,2,3,2,1,1990,'Apartment');
insert into homes values (HomeID.NEXTVAL,'98684 Main Street','Novi','MI',48331,900,1,1,1,0,1980,'Apartment');
insert into homes values (HomeID.NEXTVAL,'234 Special Street','Bloomfield','MI',32451,1000,2,2,1,0,2001,'Condo');
insert into homes values (HomeID.NEXTVAL,'77755 Super Street','Southfield','MI',43215,950,2,1,1,2,1972,'Apartment');
insert into homes values (HomeID.NEXTVAL,'124523 WhattaCool Street','Detroit','MI',12234,1500,2,3,2,12,1901,'Apartment');
insert into homes values (HomeID.NEXTVAL,'24255 El Bathy Road','Chicago','IL',54321,6000,3,5,5,15,1987,'House');
insert into homes values (HomeID.NEXTVAL,'24657 Fake Street','Dallas','TX',12345,9000,3,6,5,25,2005,'House');

--Insert data into agent table
insert into agents values (AgentID.NEXTVAL,'Aaron Kinder');
insert into agents values (AgentID.NEXTVAL,'Taylor Fouts');
insert into agents values (AgentID.NEXTVAL,'Gordon Stein');
insert into agents values (AgentID.NEXTVAL,'Tyler Smith');
insert into agents values (AgentID.NEXTVAL,'Naser El Bathy');
insert into agents values (AgentID.NEXTVAL,'John Stamos');

--Insert data into appliances table
insert into appliances values (ApplianceID.NEXTVAL,2,'Refrigerator','KitchenAid');
insert into appliances values (ApplianceID.NEXTVAL,2,'Stove','General Electric');
insert into appliances values (ApplianceID.NEXTVAL,4,'Washer','Whirlpool');
insert into appliances values (ApplianceID.NEXTVAL,5,'Dryer','Maytag');
insert into appliances values (ApplianceID.NEXTVAL,6,'Stove','Amana');

commit;
